package com.wysql.ascost.service.info;

import cn.hutool.core.util.StrUtil;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.wysql.ascost.common.base.BaseService;
import com.wysql.ascost.common.exception.demo.ExcelImportZdyException;
import com.wysql.ascost.common.exception.demo.FileNameException;
import com.wysql.ascost.common.support.ConvertUtil;
import com.wysql.ascost.mapper.info.InfoFileMapper;
import com.wysql.ascost.mapper.info.InfoImportresultDtlMapper;
import com.wysql.ascost.mapper.info.InfoImportresultMapper;
import com.wysql.ascost.mapper.info.InfoProjectbudgetJsMapper;
import com.wysql.ascost.model.auto.TsysUser;
import com.wysql.ascost.model.custom.Tablepar;
import com.wysql.ascost.model.info.*;
import com.wysql.ascost.shiro.util.ShiroUtils;
import com.wysql.ascost.util.SnowflakeIdWorker;
import com.wysql.ascost.util.StringUtils;
import com.wysql.ascost.util.TestForExcel2PDF;
import com.wysql.ascost.util.UploadFileUtil;
import org.apache.logging.log4j.util.Strings;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 工程概算台账（结算环节） InfoProjectbudgetJsService
 *
 * @author wysqlauto_自动生成
 * @Title: InfoProjectbudgetJsService.java 
 * @Package com.wysql.ascost.service 
 * @email ${email}
 * @date 2025-03-05 09:46:38  
 **/
@Service
public class InfoProjectbudgetJsService implements BaseService<InfoProjectbudgetJs, InfoProjectbudgetJsExample> {
    /**
     * 单斜线
     */
    static String singleSlash = "/";
    @Value("${file.uploadUrl}")
    private String uploadPath;
    @Value("${file.staticAccessPath}")
    private String staticAccessPath;

    @Autowired
    private InfoProjectbudgetJsMapper infoProjectbudgetJsMapper;
    @Autowired
    private InfoFileMapper infoFileMapper;
    @Autowired
    private InfoImportresultMapper infoImportresultMapper;
    @Autowired
    private InfoImportresultDtlMapper infoImportresultDtlMapper;

    /**
     * 分页查询
     *
     * @param
     * @param
     * @return
     */
    public PageInfo<InfoProjectbudgetJs> list(Tablepar tablepar, InfoProjectbudgetJs infoProjectbudgetJs) {
        InfoProjectbudgetJsExample testExample = new InfoProjectbudgetJsExample();
        InfoProjectbudgetJsExample.Criteria criteria = testExample.createCriteria();
        criteria.andIsconfirmEqualTo(infoProjectbudgetJs.getIsconfirm());
        //搜索
//        if (StrUtil.isNotEmpty(tablepar.getSearchText())) {//小窗体
//            testExample.createCriteria().andLikeQuery2(tablepar.getSearchText());
//        } else {//大搜索
//            testExample.createCriteria().andLikeQuery(infoProjectbudgetJs);
//        }
        if (StringUtils.isNotBlank(infoProjectbudgetJs.getProname())) {
            criteria.andPronameLike('%' + infoProjectbudgetJs.getProname() + '%');
        }
        if (StringUtils.isNotBlank(infoProjectbudgetJs.getPronum())) {
            criteria.andPronumLike('%' + infoProjectbudgetJs.getPronum() + '%');
        }
        if (StringUtils.isNotBlank(infoProjectbudgetJs.getBatchnum())) {
            criteria.andBatchnumLike('%' + infoProjectbudgetJs.getBatchnum() + '%');
        }
        if (infoProjectbudgetJs.getType() != null) {
            criteria.andTypeEqualTo(infoProjectbudgetJs.getType());
        }
        //表格排序
        if (StrUtil.isNotEmpty(tablepar.getOrderByColumn())) {
            testExample.setOrderByClause(StringUtils.toUnderScoreCase(tablepar.getOrderByColumn()) + " " + tablepar.getIsAsc());
        } else {
            testExample.setOrderByClause("filedate DESC");
        }
        PageHelper.startPage(tablepar.getPage(), tablepar.getLimit());
        List<InfoProjectbudgetJs> list = infoProjectbudgetJsMapper.selectByExample(testExample);
        PageInfo<InfoProjectbudgetJs> pageInfo = new PageInfo<InfoProjectbudgetJs>(list);
        return pageInfo;
    }

    @Override
    public int deleteByPrimaryKey(String ids) {

        List<String> lista = ConvertUtil.toListStrArray(ids);
        InfoProjectbudgetJsExample example = new InfoProjectbudgetJsExample();
        example.createCriteria().andIdIn(lista);
        return infoProjectbudgetJsMapper.deleteByExample(example);


    }


    @Override
    public InfoProjectbudgetJs selectByPrimaryKey(String id) {

        return infoProjectbudgetJsMapper.selectByPrimaryKey(id);

    }


    @Override
    public int updateByPrimaryKeySelective(InfoProjectbudgetJs record) {
        return infoProjectbudgetJsMapper.updateByPrimaryKeySelective(record);
    }


    /**
     * 添加
     */
    @Override
    public int insertSelective(InfoProjectbudgetJs record) {

        //添加雪花主键id
        record.setId(SnowflakeIdWorker.getUUID());


        return infoProjectbudgetJsMapper.insertSelective(record);
    }


    @Override
    public int updateByExampleSelective(InfoProjectbudgetJs record, InfoProjectbudgetJsExample example) {

        return infoProjectbudgetJsMapper.updateByExampleSelective(record, example);
    }


    @Override
    public int updateByExample(InfoProjectbudgetJs record, InfoProjectbudgetJsExample example) {

        return infoProjectbudgetJsMapper.updateByExample(record, example);
    }

    @Override
    public List<InfoProjectbudgetJs> selectByExample(InfoProjectbudgetJsExample example) {

        return infoProjectbudgetJsMapper.selectByExample(example);
    }


    @Override
    public long countByExample(InfoProjectbudgetJsExample example) {

        return infoProjectbudgetJsMapper.countByExample(example);
    }


    @Override
    public int deleteByExample(InfoProjectbudgetJsExample example) {

        return infoProjectbudgetJsMapper.deleteByExample(example);
    }

    @Transactional
    public Map<String, Object> uploadBaseSingle(MultipartFile file) throws Exception {
        System.err.println("++++++++++++++++++" + file.getOriginalFilename());
        Map<String, Object> map = new HashMap<>();
        // 0、初始化、定义执行结果变量
        String returnStr = "";
        int successNum = 0;// 成功条数
        int failNum = 0;// 失败条数

        String realname = file.getOriginalFilename();//导入原始文件名称
        //特殊符号正则处理
        String filename = this.repairName(realname);//导入原始文件名称经过特殊符号处理后名称

        // 1、导入数据获取--解析excel表格进行数据灌入
        InfoProjectbudgetJs infoProjectbudgetJs = null;

        try {
            infoProjectbudgetJs = importExcel(file.getInputStream(), file.getOriginalFilename());
        } catch (Exception e) {
            e.printStackTrace();
            if (e instanceof FileNameException) {
                String msg = ((FileNameException) e).getMsg();
                throw new ExcelImportZdyException(500, msg);
            } else {
                throw new ExcelImportZdyException(500, "概算书导入异常，请重新检查导入文件或手动新增");
            }
        }

        if (infoProjectbudgetJsMapper.insert(infoProjectbudgetJs) > 0) {
            successNum = successNum + 1;
        } else {
            failNum = failNum + 1;
        }

        if (failNum > 0) {
            map.put("code", 0);
            map.put("msg", "基建类概算书--" + realname + "导入失败");
            return map;
        } else {

            //上传文件至服务器
            String pathString = null;
            String uuid = UUID.randomUUID().toString();
            Calendar calendar = Calendar.getInstance();
            String tm = calendar.get(Calendar.YEAR) + "-" + (calendar.get(Calendar.MONTH) + 1);
            Long size = file.getSize();
            //获取上传的文件名称
            //chrome会获取到该文件的直接文件名称，IE/Edge会获取到文件上传时完整路径/文件名
            //Check for Unix-style path
            int unixSep = filename.lastIndexOf('/');
            //Check for Windows-style path
            int winSep = filename.lastIndexOf('\\');
            //cut off at latest possible point
            int pos = (winSep > unixSep ? winSep : unixSep);
            if (pos != -1) {
                filename = filename.substring(pos + 1);
            }
            String pdffilename = filename.substring(0, filename.lastIndexOf("."));
            pathString = uploadPath + tm + singleSlash + uuid + singleSlash + filename;
            String pdfpathString = uploadPath + tm + singleSlash + uuid + singleSlash + pdffilename + ".pdf";
            File files = new File(pathString);
            if (!files.getParentFile().exists()) {
                files.getParentFile().mkdirs();
            }
            file.transferTo(files);
            map.put("code", 0);
            map.put("fileUrl", staticAccessPath + tm + singleSlash + uuid + singleSlash + filename);
            map.put("fileName", filename);
            map.put("localPath", pathString);
            map.put("size", size);

            //业务文件表中增加excel文件记录
            createFileExcel(pathString, map, realname, infoProjectbudgetJs);
            //Excel转换为PDF上传至服务器文件夹
            TestForExcel2PDF.excel2pdf(pathString, pdfpathString);
            //业务文件表中增加pdf文件记录
            createFilePdf(pdfpathString, map, realname, infoProjectbudgetJs);

            map.put("code", 0);
            map.put("msg", "基建类概算书--" + realname + "--导入成功");
            return map;
        }
    }

    /**
     * @Title: repairName
     * @Author: ShengQL
     * @Date: 2024/12/17 10:19
     * @Params: [realname]
     * @Return: String
     * @Description: 对于文件名称中特殊符号的处理
     */
    private String repairName(String realname) {
        //1、目前仅处理#号
        realname = realname.replaceAll("#", "号");
        return realname;
    }

    private void createFilePdf(String pdfpathString, Map<String, Object> map, String realname, InfoProjectbudgetJs infoProjectbudgetJs) {
        //上传成功后记录附件信息至业务附件表
        InfoFile infoFile1 = new InfoFile();
        infoFile1.setId(UUID.randomUUID().toString());
        infoFile1.setLocationPath(pdfpathString);
        infoFile1.setFileUrl(map.get("fileUrl").toString().replace("xlsx", "pdf").replace("xls", "pdf"));
        infoFile1.setRealName(realname.replace("xlsx", "pdf").replace("xls", "pdf"));
        infoFile1.setFileName(map.get("fileName").toString().replace("xlsx", "pdf").replace("xls", "pdf"));
//        infoFile1.setFkSubject("2");
        infoFile1.setFkRecord(infoProjectbudgetJs.getId());
        infoFile1.setSuffix(".pdf");
        infoFile1.setCreateDate(new Date());
        infoFile1.setFkTaskId("3");
        infoFileMapper.insertSelective(infoFile1);
    }

    private void createFileExcel(String pathString, Map<String, Object> map, String realname, InfoProjectbudgetJs infoProjectbudgetJs) {
        //新增业务文件台账记录，记录文件地址等信息
        //上传成功后记录附件信息至业务附件表
        InfoFile infoFile = new InfoFile();
        infoFile.setId(UUID.randomUUID().toString());
        infoFile.setLocationPath(pathString);
        infoFile.setFileUrl(map.get("fileUrl").toString());
        infoFile.setFileSize(Integer.valueOf(map.get("size").toString()));
        //处理后的文件名
        infoFile.setFileName(map.get("fileName").toString());
        infoFile.setRealName(realname);
//        infoFile.setFkSubject("1");
        infoFile.setFkRecord(infoProjectbudgetJs.getId());
        infoFile.setSuffix(realname.substring(realname.lastIndexOf(".")));
        infoFile.setCreateDate(new Date());
        infoFile.setFkTaskId("3");
        infoFileMapper.insertSelective(infoFile);
    }

    public InfoProjectbudgetJs importExcel(InputStream inputStream, String name) throws Exception {
        InfoProjectbudgetJs infoProjectbudgetJs = new InfoProjectbudgetJs();
        // 1、传入获取的工作流, workbook,是一个工作簿，使用excel能操作的这边它都可以操作，这一个操作，相当于是把Excel放到Java中使用
        // Workbook wb = new HSSFWorkbook(inputStream);
        Workbook wb = null;
        String extString = name.substring(name.lastIndexOf("."));
        if (".xls".equals(extString)) {
            wb = new HSSFWorkbook(inputStream);
        } else if (".xlsx".equals(extString)) {
            wb = new XSSFWorkbook(inputStream);
        }
        // 2、获取工作簿中的表,对表中的设置，通过下标去拿第一张表
//        Sheet sheet = wb.getSheetAt(0);//封面0
        Sheet sheet = wb.getSheet("封面");//封面0
        Sheet sheet1 = wb.getSheet("总算表(表一)");//总算表(表一)3
        Sheet sheet2 = wb.getSheet("其他费用表(表四)");//其他费用表(表四)13
        Sheet sheet3 = wb.getSheet("拆除工程取费表");//拆除工程取费表
        Sheet sheet4 = wb.getSheet("甲供主材汇总表");//拆除工程取费表
        Sheet sheet5 = wb.getSheet("安装工程部分汇总表(表二甲)");

        if (sheet != null) {
            //概算书名称--封面--A3
            Row rowData = sheet.getRow(2);
            infoProjectbudgetJs.setProname(getCellFormatValue(rowData.getCell(0)).trim());
            //概算书日期--封面--A6
            Row rowData1 = sheet.getRow(5);
            Cell cell = rowData1.getCell(0);
            System.err.println(cell.getCellType());
            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    //这里一直没进来，待观测
                    String cellFormatValue = getCellFormatValue(cell).trim();
                    infoProjectbudgetJs.setBookdate(cellFormatValue);
                } else {
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
                    String format = sdf.format(date);
                    infoProjectbudgetJs.setBookdate(format);
                }
            } else {
                String cellFormatValue = getCellFormatValue(cell).trim();
                infoProjectbudgetJs.setBookdate(cellFormatValue);
            }
        } else {
            infoProjectbudgetJs.setProname(null);
            infoProjectbudgetJs.setBookdate(null);
        }

        if (Strings.isBlank(infoProjectbudgetJs.getProname())) {
            throw new FileNameException(500, "概算书导入异常，概算书项目名称为空");
        } else {
            //根据概算书封面名称判断是否重复导入
            InfoProjectbudgetJsExample infoProjectbudgetJsExample = new InfoProjectbudgetJsExample();
            infoProjectbudgetJsExample.createCriteria().andPronameEqualTo(infoProjectbudgetJs.getProname());
            List<InfoProjectbudgetJs> infoProjectbudgetJss = infoProjectbudgetJsMapper.selectByExample(infoProjectbudgetJsExample);
            if (infoProjectbudgetJss != null && infoProjectbudgetJss.size() > 0) {
                throw new FileNameException(500, "概算书导入异常，概算书项目名称重复无法重复导入");
            } else {
                if (sheet1 != null) {
                    //建筑工程费--总算表(表一)--C23
                    Row rowDatajzgcf = sheet1.getRow(22);
                    String trimC23 = getCellFormatValue(rowDatajzgcf.getCell(2)).trim();
                    trimC23 = Strings.isBlank(trimC23) ? "0" : trimC23;
                    infoProjectbudgetJs.setJzgcf(new BigDecimal(trimC23).multiply(new BigDecimal("10000")));
                    //安装工程费  总算表(表一)-E23
                    String trimE23 = getCellFormatValue(rowDatajzgcf.getCell(4)).trim();
                    trimE23 = Strings.isBlank(trimE23) ? "0" : trimE23;
                    infoProjectbudgetJs.setAzgcf(new BigDecimal(trimE23).multiply(new BigDecimal("10000")));
                    //设备购置费  总算表(表一)-D23
                    String trimD23 = getCellFormatValue(rowDatajzgcf.getCell(3)).trim();
                    trimD23 = Strings.isBlank(trimD23) ? "0" : trimD23;
                    infoProjectbudgetJs.setSbgzf(new BigDecimal(trimD23).multiply(new BigDecimal("10000")));
                    //其他费	总算表(表一)-F23
                    String trimF23 = getCellFormatValue(rowDatajzgcf.getCell(5)).trim();
                    trimF23 = Strings.isBlank(trimF23) ? "0" : trimF23;
                    infoProjectbudgetJs.setQtf(new BigDecimal(trimF23).multiply(new BigDecimal("10000")));
                    //总投资 总算表(表一)-H23
                    String trimH23 = getCellFormatValue(rowDatajzgcf.getCell(7)).trim();
                    trimH23 = Strings.isBlank(trimH23) ? "0" : trimH23;
                    infoProjectbudgetJs.setTotal(new BigDecimal(trimH23).multiply(new BigDecimal("10000")));
                    //建设场地征用及清理费	总算表(表一)-H12
                    Row rowData2 = sheet1.getRow(11);
                    String trimH12 = getCellFormatValue(rowData2.getCell(7)).trim();
                    trimH12 = Strings.isBlank(trimH12) ? "0" : trimH12;
                    infoProjectbudgetJs.setJscdzyjglf(new BigDecimal(trimH12).multiply(new BigDecimal("10000")));
                    //项目建设管理费	总算表(表一)-H13
                    Row rowData3 = sheet1.getRow(12);
                    String trimH13 = getCellFormatValue(rowData3.getCell(7)).trim();
                    trimH13 = Strings.isBlank(trimH13) ? "0" : trimH13;
                    infoProjectbudgetJs.setXmjsglf(new BigDecimal(trimH13).multiply(new BigDecimal("10000")));
                    //建设期贷款利息	总算表(表一)-H22
                    Row rowDatajsqdk = sheet1.getRow(21);
                    String trimH22 = getCellFormatValue(rowDatajsqdk.getCell(7)).trim();
                    trimH22 = Strings.isBlank(trimH22) ? "0" : trimH22;
                    infoProjectbudgetJs.setJsqdklx(new BigDecimal(trimH22).multiply(new BigDecimal("10000")));
                } else {
                    infoProjectbudgetJs.setJzgcf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setAzgcf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSbgzf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setQtf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setTotal(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJscdzyjglf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setXmjsglf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJsqdklx(BigDecimal.ZERO);
                }

                if (sheet2 != null) {
                    for (Row cell : sheet2) {
                        String proName = getCellFormatValue(cell.getCell(1));
//                System.err.println(proName);
                        if ("项目管理经费".equals(proName)) {
                            System.err.println(infoProjectbudgetJs.getXmgljf());
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setXmgljf(new BigDecimal(value));
                        } else if ("工程监理费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setGcjlf(new BigDecimal(value));
                        } else if ("项目前期工作费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setXmqqgzf(new BigDecimal(value));
                        } else if ("勘察费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setKcf(new BigDecimal(value));
                        } else if ("设计费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setSjf(new BigDecimal(value));
                        } else if ("设计文件评审费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setSjwjpsf(new BigDecimal(value));
                        } else if ("非电缆工程项目后评价费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setFdlgcxmhpjf(new BigDecimal(value));
                        } else if ("电缆工程项目后评价费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setDlgcxmhpjf(new BigDecimal(value));
                        } else if ("工程保险费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setGcbxf(new BigDecimal(value));
                        } else if ("施工过程造价咨询及竣工结算审核费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setJgjsshf(new BigDecimal(value));
                        } else if ("生产准备费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setSczbf(new BigDecimal(value));
                        }
                    }
                    //遍历后对以上数据未获取到的，进行补0
                    infoProjectbudgetJs = setDefaultValue(infoProjectbudgetJs);
                } else {
                    infoProjectbudgetJs.setXmgljf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setGcjlf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSgf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setXmqqgzf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setKcf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSjf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSjwjpsf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setFdlgcxmhpjf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setDlgcxmhpjf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setGcbxf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJgjsshf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSczbf(BigDecimal.ZERO);
                }

                if (sheet3 != null) {
                    for (Row cells : sheet3) {
                        String proName = getCellFormatValue(cells.getCell(1));
                        if ("合计".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cells.getCell(4)).trim()) ? "0" : getCellFormatValue(cells.getCell(4)).trim();
                            infoProjectbudgetJs.setCcgcfhj(new BigDecimal(value));
                        }
                    }
                    if (infoProjectbudgetJs.getCcgcfhj() == null) {
                        infoProjectbudgetJs.setCcgcfhj(BigDecimal.ZERO);
                    }
                } else {
                    infoProjectbudgetJs.setCcgcfhj(BigDecimal.ZERO);
                }

                if (sheet4 != null) {
                    for (Row cells : sheet4) {
                        String proName = getCellFormatValue(cells.getCell(1));
                        if ("合计".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cells.getCell(11)).trim()) ? "0" : getCellFormatValue(cells.getCell(11)).trim();
                            infoProjectbudgetJs.setJgchj(new BigDecimal(value));
                            infoProjectbudgetJs.setJgchjje(new BigDecimal(value));
                        }
                    }
                    if (infoProjectbudgetJs.getJgchj() == null) {
                        infoProjectbudgetJs.setJgchj(BigDecimal.ZERO);
                        infoProjectbudgetJs.setJgchjje(BigDecimal.ZERO);
                    }
                } else {
                    infoProjectbudgetJs.setJgchj(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJgchjje(BigDecimal.ZERO);
                }

                if (sheet5 != null) {
                    for (Row cells : sheet5) {
                        String proName = getCellFormatValue(cells.getCell(1));
                        if ("带电作业".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cells.getCell(6)).trim()) ? "0" : getCellFormatValue(cells.getCell(6)).trim();
                            infoProjectbudgetJs.setDdzyf(new BigDecimal(value));
                        }
                    }
                    if (infoProjectbudgetJs.getDdzyf() == null) {
                        infoProjectbudgetJs.setDdzyf(BigDecimal.ZERO);
                    }
                } else {
                    infoProjectbudgetJs.setDdzyf(BigDecimal.ZERO);
                }

                //项目后评价费	非电缆工程项目后评价费+电缆工程项目后评价费
                infoProjectbudgetJs.setXmhpjf(infoProjectbudgetJs.getFdlgcxmhpjf().add(infoProjectbudgetJs.getDlgcxmhpjf()));
                //施工费	建筑工程费+安装工程费+拆除工程费-甲供材
                //拆除工程取费表—合计
                //甲供主材汇总表-合计
//                infoProjectbudgetJs.setSgf(infoProjectbudgetJs.getJzgcf().add(infoProjectbudgetJs.getAzgcf()).add(infoProjectbudgetJs.getCcgcfhj()).subtract(infoProjectbudgetJs.getJgchj()));
                infoProjectbudgetJs.setSgf(infoProjectbudgetJs.getJzgcf().add(infoProjectbudgetJs.getAzgcf()).subtract(infoProjectbudgetJs.getJgchj()));
                //=============数据封装=====================
                infoProjectbudgetJs.setId(SnowflakeIdWorker.getUUID());//ID
                infoProjectbudgetJs.setFiledate(new Date());
                infoProjectbudgetJs.setType(1);
                infoProjectbudgetJs.setCreatetype(1);
                infoProjectbudgetJs.setIsconfirm(0);
                infoProjectbudgetJs.setFilename(name);
            }

        }
        // 关闭流
        inputStream.close();
        // 返回list结果
        return infoProjectbudgetJs;
    }

    private InfoProjectbudgetJs setDefaultValue(InfoProjectbudgetJs infoProjectbudgetJs) {
        if (infoProjectbudgetJs.getXmgljf() == null) {
            infoProjectbudgetJs.setXmgljf(BigDecimal.ZERO);
        }
        if (infoProjectbudgetJs.getGcjlf() == null) {
            infoProjectbudgetJs.setGcjlf(BigDecimal.ZERO);
        }
        if (infoProjectbudgetJs.getXmqqgzf() == null) {
            infoProjectbudgetJs.setXmqqgzf(BigDecimal.ZERO);
        }
        if (infoProjectbudgetJs.getKcf() == null) {
            infoProjectbudgetJs.setKcf(BigDecimal.ZERO);
        }
        if (infoProjectbudgetJs.getSjf() == null) {
            infoProjectbudgetJs.setSjf(BigDecimal.ZERO);
        }
        if (infoProjectbudgetJs.getSjwjpsf() == null) {
            infoProjectbudgetJs.setSjwjpsf(BigDecimal.ZERO);
        }
        if (infoProjectbudgetJs.getDlgcxmhpjf() == null) {
            infoProjectbudgetJs.setDlgcxmhpjf(BigDecimal.ZERO);
        }
        if (infoProjectbudgetJs.getFdlgcxmhpjf() == null) {
            infoProjectbudgetJs.setFdlgcxmhpjf(BigDecimal.ZERO);
        }
        if (infoProjectbudgetJs.getGcbxf() == null) {
            infoProjectbudgetJs.setGcbxf(BigDecimal.ZERO);
        }
        if (infoProjectbudgetJs.getJgjsshf() == null) {
            infoProjectbudgetJs.setJgjsshf(BigDecimal.ZERO);
        }
        if (infoProjectbudgetJs.getSczbf() == null) {
            infoProjectbudgetJs.setSczbf(BigDecimal.ZERO);
        }
        //20250428 增加建设场地征用及清理费赋值，针对技改类导入
        if (infoProjectbudgetJs.getJscdzyjglf() == null) {
            infoProjectbudgetJs.setJscdzyjglf(BigDecimal.ZERO);
        }
        return infoProjectbudgetJs;
    }

    public static String importExcel(Cell cell, Workbook wb) throws IOException {
        String value = "";
        //后面使用它来执行计算公式
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                value = String.valueOf(cell.getNumericCellValue()).substring(0, String.valueOf(cell.getNumericCellValue()).length() - 2);
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            case Cell.CELL_TYPE_FORMULA:
//             value = String.valueOf(cell.getNumericCellValue());
                String cellFormula = cell.getCellFormula();
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                value = Objects.equals("#N/A", evaluate.formatAsString()) ? "" : evaluate.formatAsString();
                break;
            default:
                value = cell.toString();
                break;
        }
        return value;
    }

    //这个方法对cell进行处理，传入cell对象，返回cell中内容，String类型。
    public static String getCellFormatValue(Cell cell) {
        String cellValue = "";
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case Cell.CELL_TYPE_STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }
        return cellValue;
    }


    public int importBatch(String ids) {
        int a = 0;
        List<String> lista = ConvertUtil.toListStrArray(ids);
        for (String s : lista) {
            InfoProjectbudgetJs infoProjectbudgetJs = infoProjectbudgetJsMapper.selectByPrimaryKey(s);
            infoProjectbudgetJs.setIsconfirm(1);
            infoProjectbudgetJs.setConfirmdate(new Date());
            infoProjectbudgetJsMapper.updateByPrimaryKey(infoProjectbudgetJs);
            a++;
        }
        return a;
    }


    /**
     * @Title: uploadYkSingle
     * @Author: ShengQL
     * @Date: 2024/12/16 9:21
     * @Params: [file]
     * @Return: Map<String, Object>
     * @Description: 业扩类概算书导入
     */
    public Map<String, Object> uploadYkSingle(MultipartFile file) {
        Map<String, Object> map = new HashMap<>();
//        // 0、初始化、定义执行结果变量
//        String returnStr = "";
//        int successNum = 0;// 成功条数
//        int failNum = 0;// 失败条数
//        int repeatNum = 0;// 重复条数
//        int createNum = 0;// 生成的关联项管理提升记录条数
//
//        // 1、导入数据获取
//        InfoProjectbudgetJs infoProjectbudgetJs = importExcel(file.getInputStream(), file.getOriginalFilename());
//
//        if (infoProjectbudgetJsMapper.insert(infoProjectbudgetJs) > 0) {
//            successNum = successNum + 1;
//        }
//        if (failNum > 0) {
//            map.put("code", 0);
//            map.put("msg", "导入成功[" + successNum + "]条数据，导入失败[" + failNum + "]条数据，关联性指标自动生成[" + createNum + "]条数据，<br>" + returnStr);
//            return map;
//        } else {
//
//            //上传文件至服务器
//            String pathString = null;
//            String uuid = UUID.randomUUID().toString();
//            Calendar calendar = Calendar.getInstance();
//            String tm = calendar.get(Calendar.YEAR) + "-" + (calendar.get(Calendar.MONTH) + 1);
//            Long size = file.getSize();
//            //获取上传的文件名称
//            String filename = file.getOriginalFilename();
//            //chrome会获取到该文件的直接文件名称，IE/Edge会获取到文件上传时完整路径/文件名
//            //Check for Unix-style path
//            int unixSep = filename.lastIndexOf('/');
//            //Check for Windows-style path
//            int winSep = filename.lastIndexOf('\\');
//            //cut off at latest possible point
//            int pos = (winSep > unixSep ? winSep : unixSep);
//            if (pos != -1) {
//                filename = filename.substring(pos + 1);
//            }
//            String pdffilename = filename.substring(0,filename.lastIndexOf("."));
//            pathString = uploadPath + tm + singleSlash + uuid + singleSlash + filename;
//            String pdfpathString = uploadPath + tm + singleSlash + uuid + singleSlash + pdffilename+".pdf";
//            File files = new File(pathString);
//            if (!files.getParentFile().exists()) {
//                files.getParentFile().mkdirs();
//            }
//            file.transferTo(files);
//            map.put("code", 0);
//            map.put("fileUrl", staticAccessPath + tm + singleSlash + uuid + singleSlash + filename);
//            map.put("fileName", filename);
//            map.put("localPath", pathString);
//            map.put("size", size);
//
//            //新增业务文件台账记录，记录文件地址等信息
//            //上传成功后记录附件信息至业务附件表
//            InfoFile infoFile = new InfoFile();
//            infoFile.setId(UUID.randomUUID().toString());
//            infoFile.setLocationPath(pathString);
//            infoFile.setFileUrl(map.get("fileUrl").toString());
//            infoFile.setFileSize(Integer.valueOf(map.get("size").toString()));
//            infoFile.setFileName(filename);
//            infoFile.setFkSubject("1");
//            infoFile.setFkRecord(infoProjectbudgetJs.getId());
////            if (fkSubject.equals("3")) {
////                infoFile.setFkTaskId(taskid);
////            }
//            infoFile.setSuffix(filename.substring(filename.lastIndexOf(".")));
//            infoFile.setCreateDate(new Date());
//            infoFileMapper.insertSelective(infoFile);
//
//
//            //Excel转换为PDF上传至服务器文件夹
//            TestForExcel2PDF.excel2pdf(pathString,pdfpathString);
//
//            //上传成功后记录附件信息至业务附件表
//            InfoFile infoFile1 = new InfoFile();
//            infoFile1.setId(UUID.randomUUID().toString());
//            infoFile1.setLocationPath(pdfpathString);
//            infoFile1.setFileUrl(map.get("fileUrl").toString().replace("xlsx","pdf"));
////            infoFile1.setFileSize(Integer.valueOf(map.get("size").toString()));
//            infoFile1.setFileName(filename.replace("xlsx","pdf"));
//            infoFile1.setFkSubject("2");
//            infoFile1.setFkRecord(infoProjectbudgetJs.getId());
////            if (fkSubject.equals("3")) {
////                infoFile.setFkTaskId(taskid);
////            }
//            infoFile1.setSuffix(".pdf");
//            infoFile1.setCreateDate(new Date());
//            infoFileMapper.insertSelective(infoFile1);
//
//
//            map.put("code", 0);
//            map.put("msg", "导入成功");
        return map;
    }

    /**
     * @Title: finddistinct
     * @Author: ShengQL
     * @Date: 2024/12/16 14:11
     * @Params: [file, type]
     * @Return: Boolean
     * @Description: TODO 根据文件名称及文件类型确定唯一
     */
    public Boolean finddistinct(MultipartFile file, Integer type) {
        Boolean flag = false;

        String filename = file.getOriginalFilename();
        //获取上传的文件名称
        //chrome会获取到该文件的直接文件名称，IE/Edge会获取到文件上传时完整路径/文件名
        //Check for Unix-style path
        int unixSep = filename.lastIndexOf('/');
        //Check for Windows-style path
        int winSep = filename.lastIndexOf('\\');
        //cut off at latest possible point
        int pos = (winSep > unixSep ? winSep : unixSep);
        if (pos != -1) {
            filename = filename.substring(pos + 1);
        }
//        String proname = filename.substring(0, filename.lastIndexOf("."));

        InfoProjectbudgetJsExample infoProjectbudgetJsExample = new InfoProjectbudgetJsExample();
        infoProjectbudgetJsExample.createCriteria().andFilenameEqualTo(filename).andTypeEqualTo(type);
        List<InfoProjectbudgetJs> infoProjectbudgetJss = infoProjectbudgetJsMapper.selectByExample(infoProjectbudgetJsExample);
        if (infoProjectbudgetJss != null && infoProjectbudgetJss.size() > 0) {
            flag = true;
        }
        return flag;
    }

    public Boolean findisexist(InfoProjectbudgetJs infoProjectbudgetJs) {
        Boolean res = false;
        InfoProjectbudgetJsExample infoProjectbudgetJsExample = new InfoProjectbudgetJsExample();
        infoProjectbudgetJsExample.createCriteria().andPronameEqualTo(infoProjectbudgetJs.getProname());
        List<InfoProjectbudgetJs> infoProjectbudgetJss = infoProjectbudgetJsMapper.selectByExample(infoProjectbudgetJsExample);
        if (infoProjectbudgetJss != null && infoProjectbudgetJss.size() > 0) {
            res = true;
        }
        return res;
    }

    /**
     * @Title: uploadBatchJj
     * @Author: ShengQL
     * @Date: 2025/2/26 9:41
     * @Params: [files]
     * @Return: Map<String, Object>
     * @Description: 基建类概算书批量导入
     */
    public Map<String, Object> uploadBatchJj(MultipartFile[] files, String batchnum, Integer type) {
        //0、初始化
        Map<String, Object> map = new HashMap<>();
        int total = files.length;//总数
        int successfulCount = 0;//成功条数
        int failureCount = 0;//失败条数
        InfoImportresult infoImportresult = new InfoImportresult();
        infoImportresult.setId(SnowflakeIdWorker.getUUID());//先生成主表id供插入明细表用

        //1、遍历进行数据导入、文件转换
        for (MultipartFile file : files) {
            String realname = file.getOriginalFilename();//导入原始文件名称
            //特殊符号正则处理
            String filename = this.repairName(realname);//导入原始文件名称

            InfoImportresultDtl infoImportresultDtl = new InfoImportresultDtl();
            infoImportresultDtl.setId(SnowflakeIdWorker.getUUID());
            infoImportresultDtl.setFilename(filename);
            infoImportresultDtl.setMainid(infoImportresult.getId());
            infoImportresultDtl.setBatchnum(batchnum);
            infoImportresultDtl.setCreatetime(new Date());
            infoImportresultDtl.setStep(3);
            infoImportresultDtl.setType(type);

            //文件做重复性校验
            InfoProjectbudgetJs budget = new InfoProjectbudgetJs();
            budget.setProname(filename.trim());
            //判别名称是否重复
            Boolean isexist = this.findisexist(budget);
            if (isexist) {
                failureCount++;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("名称为《" + realname + "》的文件已经导入，无法重复导入");
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
                continue;
            }

            // 1、导入数据获取--解析excel表格进行数据灌入
            InfoProjectbudgetJs infoProjectbudgetJs = null;
            try {
                infoProjectbudgetJs = importExcel(file.getInputStream(), file.getOriginalFilename());
                infoProjectbudgetJs.setBatchnum(batchnum);
            } catch (Exception e) {
                e.printStackTrace();
                failureCount++;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("Error processing file " + file.getOriginalFilename() + ": " + e.getMessage());
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
                continue;
            }

            //上传文件至服务器
            String pathString = null;
            String pdfpathString = null;
            try {
                pathString = null;
                String uuid = UUID.randomUUID().toString();
                Calendar calendar = Calendar.getInstance();
                String tm = calendar.get(Calendar.YEAR) + "-" + (calendar.get(Calendar.MONTH) + 1);
                Long size = file.getSize();
                //获取上传的文件名称
                //chrome会获取到该文件的直接文件名称，IE/Edge会获取到文件上传时完整路径/文件名
                //Check for Unix-style path
                int unixSep = filename.lastIndexOf('/');
                //Check for Windows-style path
                int winSep = filename.lastIndexOf('\\');
                //cut off at latest possible point
                int pos = (winSep > unixSep ? winSep : unixSep);
                if (pos != -1) {
                    filename = filename.substring(pos + 1);
                }
                String pdffilename = filename.substring(0, filename.lastIndexOf("."));
                pathString = uploadPath + tm + singleSlash + uuid + singleSlash + filename;
                pdfpathString = uploadPath + tm + singleSlash + uuid + singleSlash + pdffilename + ".pdf";
                File orifiles = new File(pathString);
                if (!orifiles.getParentFile().exists()) {
                    orifiles.getParentFile().mkdirs();
                }
                file.transferTo(orifiles);
                map.put("code", 0);
                map.put("fileUrl", staticAccessPath + tm + singleSlash + uuid + singleSlash + filename);
                map.put("fileName", filename);
                map.put("localPath", pathString);
                map.put("size", size);

                //业务文件表中增加excel文件记录
                createFileExcel(pathString, map, realname, infoProjectbudgetJs);
                //Excel转换为PDF上传至服务器文件夹
                TestForExcel2PDF.excel2pdf(pathString, pdfpathString);
                //业务文件表中增加pdf文件记录
                createFilePdf(pdfpathString, map, realname, infoProjectbudgetJs);
            } catch (IOException e) {
                e.printStackTrace();
                failureCount++;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("附件上传服务器失败" + e.getMessage());
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
                continue;
            }

            //插入业务表
            if (infoProjectbudgetJsMapper.insert(infoProjectbudgetJs) > 0) {
                successfulCount = successfulCount + 1;
                infoImportresultDtl.setStatus(1);
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
            } else {
                failureCount = failureCount + 1;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("新增插入至概算表失败");
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
            }

        }

        //2、导入结果主表及明细表导入
        infoImportresult.setBatchnum(batchnum);
        infoImportresult.setTotal(total);
        infoImportresult.setSuccesscount(successfulCount);
        infoImportresult.setFailcount(failureCount);
        infoImportresult.setCreatetime(new Date());
        infoImportresult.setStep(3);
        infoImportresult.setType(type);
        infoImportresultMapper.insertSelective(infoImportresult);
        //3、最终结果返回
        map.put("code", 0);
        if (failureCount > 0) {
            map.put("msg", "本次导入共选择" + files.length + "项基建类概算书,成功导入" + successfulCount + "项，失败" + failureCount + "项");
        } else {
            map.put("msg", "本次导入共选择" + files.length + "项基建类概算书,全部导入成功");
        }
        return map;
    }

    public Map<String, Object> uploadYkBatch(MultipartFile[] files, String batchnum, Integer type) {
        //0、初始化
        Map<String, Object> map = new HashMap<>();
        int total = files.length;//总数
        int successfulCount = 0;//成功条数
        int failureCount = 0;//失败条数
        InfoImportresult infoImportresult = new InfoImportresult();
        infoImportresult.setId(SnowflakeIdWorker.getUUID());//先生成主表id供插入明细表用

        //1、遍历进行数据导入、文件转换
        for (MultipartFile file : files) {
            String realname = file.getOriginalFilename();//导入原始文件名称
            //特殊符号正则处理
            String filename = this.repairName(realname);//导入原始文件名称

            InfoImportresultDtl infoImportresultDtl = new InfoImportresultDtl();
            infoImportresultDtl.setId(SnowflakeIdWorker.getUUID());
            infoImportresultDtl.setFilename(filename);
            infoImportresultDtl.setMainid(infoImportresult.getId());
            infoImportresultDtl.setBatchnum(batchnum);
            infoImportresultDtl.setCreatetime(new Date());
            infoImportresultDtl.setStep(3);
            infoImportresultDtl.setType(type);

            //文件做重复性校验
            InfoProjectbudgetJs budget = new InfoProjectbudgetJs();
            budget.setProname(filename.trim());
            //判别名称是否重复
            Boolean isexist = this.findisexist(budget);
            if (isexist) {
                failureCount++;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("名称为《" + realname + "》的文件已经导入，无法重复导入");
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
                continue;
            }

            // 1、导入数据获取--解析excel表格进行数据灌入
            InfoProjectbudgetJs infoProjectbudgetJs = null;
            try {
                infoProjectbudgetJs = importExcelYk(file.getInputStream(), file.getOriginalFilename());
                infoProjectbudgetJs.setBatchnum(batchnum);
            } catch (Exception e) {
                e.printStackTrace();
                failureCount++;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("Error processing file " + file.getOriginalFilename() + ": " + e.getMessage());
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
                continue;
            }

            //上传文件至服务器
            String pathString = null;
            String pdfpathString = null;
            try {
                pathString = null;
                String uuid = UUID.randomUUID().toString();
                Calendar calendar = Calendar.getInstance();
                String tm = calendar.get(Calendar.YEAR) + "-" + (calendar.get(Calendar.MONTH) + 1);
                Long size = file.getSize();
                //获取上传的文件名称
                //chrome会获取到该文件的直接文件名称，IE/Edge会获取到文件上传时完整路径/文件名
                //Check for Unix-style path
                int unixSep = filename.lastIndexOf('/');
                //Check for Windows-style path
                int winSep = filename.lastIndexOf('\\');
                //cut off at latest possible point
                int pos = (winSep > unixSep ? winSep : unixSep);
                if (pos != -1) {
                    filename = filename.substring(pos + 1);
                }
                String pdffilename = filename.substring(0, filename.lastIndexOf("."));
                pathString = uploadPath + tm + singleSlash + uuid + singleSlash + filename;
                pdfpathString = uploadPath + tm + singleSlash + uuid + singleSlash + pdffilename + ".pdf";
                File orifiles = new File(pathString);
                if (!orifiles.getParentFile().exists()) {
                    orifiles.getParentFile().mkdirs();
                }
                file.transferTo(orifiles);
                map.put("code", 0);
                map.put("fileUrl", staticAccessPath + tm + singleSlash + uuid + singleSlash + filename);
                map.put("fileName", filename);
                map.put("localPath", pathString);
                map.put("size", size);

                //业务文件表中增加excel文件记录
                createFileExcel(pathString, map, realname, infoProjectbudgetJs);
                //Excel转换为PDF上传至服务器文件夹
                TestForExcel2PDF.excel2pdf(pathString, pdfpathString);
                //业务文件表中增加pdf文件记录
                createFilePdf(pdfpathString, map, realname, infoProjectbudgetJs);
            } catch (IOException e) {
                e.printStackTrace();
                failureCount++;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("附件上传服务器失败" + e.getMessage());
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
                continue;
            }

            //插入业务表
            if (infoProjectbudgetJsMapper.insert(infoProjectbudgetJs) > 0) {
                successfulCount = successfulCount + 1;
                infoImportresultDtl.setStatus(1);
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
            } else {
                failureCount = failureCount + 1;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("新增插入至概算表失败");
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
            }

        }

        //2、导入结果主表及明细表导入
        infoImportresult.setBatchnum(batchnum);
        infoImportresult.setTotal(total);
        infoImportresult.setSuccesscount(successfulCount);
        infoImportresult.setFailcount(failureCount);
        infoImportresult.setCreatetime(new Date());
        infoImportresult.setStep(3);
        infoImportresult.setType(type);
        infoImportresultMapper.insertSelective(infoImportresult);
        //3、最终结果返回
        map.put("code", 0);
        if (failureCount > 0) {
            map.put("msg", "本次导入共选择" + files.length + "项业扩类概算书,成功导入" + successfulCount + "项，失败" + failureCount + "项");
        } else {
            map.put("msg", "本次导入共选择" + files.length + "项业扩类概算书,全部导入成功");
        }
        return map;
    }

    public InfoProjectbudgetJs importExcelYk(InputStream inputStream, String name) throws Exception {
        InfoProjectbudgetJs infoProjectbudgetJs = new InfoProjectbudgetJs();
        // 1、传入获取的工作流, workbook,是一个工作簿，使用excel能操作的这边它都可以操作，这一个操作，相当于是把Excel放到Java中使用
        // Workbook wb = new HSSFWorkbook(inputStream);
        Workbook wb = null;
        String extString = name.substring(name.lastIndexOf("."));
        if (".xls".equals(extString)) {
            wb = new HSSFWorkbook(inputStream);
        } else if (".xlsx".equals(extString)) {
            wb = new XSSFWorkbook(inputStream);
        }
        // 2、获取工作簿中的表,对表中的设置，通过下标去拿第一张表
//        Sheet sheet = wb.getSheetAt(0);//封面0
        Sheet sheet = wb.getSheet("封面");//封面0
        Sheet sheet1 = wb.getSheet("总算表(表一)");//总算表(表一)3
        Sheet sheet2 = wb.getSheet("其他费用表(表四)");//其他费用表(表四)13
        Sheet sheet3 = wb.getSheet("拆除工程取费表");//拆除工程取费表
        Sheet sheet4 = wb.getSheet("甲供主材汇总表");//拆除工程取费表
        Sheet sheet5 = wb.getSheet("安装工程部分汇总表(表二甲)");

        if (sheet != null) {
            //概算书名称--封面--A3
            Row rowData = sheet.getRow(2);
            infoProjectbudgetJs.setProname(getCellFormatValue(rowData.getCell(0)).trim());
            //概算书日期--封面--A6
            Row rowData1 = sheet.getRow(5);
            Cell cell = rowData1.getCell(0);
            System.err.println(cell.getCellType());
            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    //这里一直没进来，待观测
                    String cellFormatValue = getCellFormatValue(cell).trim();
                    infoProjectbudgetJs.setBookdate(cellFormatValue);
                } else {
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
                    String format = sdf.format(date);
                    infoProjectbudgetJs.setBookdate(format);
                }
            } else {
                String cellFormatValue = getCellFormatValue(cell).trim();
                infoProjectbudgetJs.setBookdate(cellFormatValue);
            }
        } else {
            infoProjectbudgetJs.setProname(null);
            infoProjectbudgetJs.setBookdate(null);
        }

        if (Strings.isBlank(infoProjectbudgetJs.getProname())) {
            throw new FileNameException(500, "概算书导入异常，概算书项目名称为空");
        } else {
            //根据概算书封面名称判断是否重复导入
            InfoProjectbudgetJsExample infoProjectbudgetJsExample = new InfoProjectbudgetJsExample();
            infoProjectbudgetJsExample.createCriteria().andPronameEqualTo(infoProjectbudgetJs.getProname());
            List<InfoProjectbudgetJs> infoProjectbudgetJss = infoProjectbudgetJsMapper.selectByExample(infoProjectbudgetJsExample);
            if (infoProjectbudgetJss != null && infoProjectbudgetJss.size() > 0) {
                throw new FileNameException(500, "概算书导入异常，概算书项目名称重复无法重复导入");
            } else {
                if (sheet1 != null) {
                    //建筑工程费--总算表(表一)--C23
                    Row rowDatajzgcf = sheet1.getRow(22);
                    String trimC23 = getCellFormatValue(rowDatajzgcf.getCell(2)).trim();
                    trimC23 = Strings.isBlank(trimC23) ? "0" : trimC23;
                    infoProjectbudgetJs.setJzgcf(new BigDecimal(trimC23).multiply(new BigDecimal("10000")));
                    //安装工程费  总算表(表一)-E23
                    String trimE23 = getCellFormatValue(rowDatajzgcf.getCell(4)).trim();
                    trimE23 = Strings.isBlank(trimE23) ? "0" : trimE23;
                    infoProjectbudgetJs.setAzgcf(new BigDecimal(trimE23).multiply(new BigDecimal("10000")));
                    //设备购置费  总算表(表一)-D23
                    String trimD23 = getCellFormatValue(rowDatajzgcf.getCell(3)).trim();
                    trimD23 = Strings.isBlank(trimD23) ? "0" : trimD23;
                    infoProjectbudgetJs.setSbgzf(new BigDecimal(trimD23).multiply(new BigDecimal("10000")));
                    //其他费	总算表(表一)-F23
                    String trimF23 = getCellFormatValue(rowDatajzgcf.getCell(5)).trim();
                    trimF23 = Strings.isBlank(trimF23) ? "0" : trimF23;
                    infoProjectbudgetJs.setQtf(new BigDecimal(trimF23).multiply(new BigDecimal("10000")));
                    //总投资 总算表(表一)-H23
                    String trimH23 = getCellFormatValue(rowDatajzgcf.getCell(7)).trim();
                    trimH23 = Strings.isBlank(trimH23) ? "0" : trimH23;
                    infoProjectbudgetJs.setTotal(new BigDecimal(trimH23).multiply(new BigDecimal("10000")));
                    //建设场地征用及清理费	总算表(表一)-H12
                    Row rowData2 = sheet1.getRow(11);
                    String trimH12 = getCellFormatValue(rowData2.getCell(7)).trim();
                    trimH12 = Strings.isBlank(trimH12) ? "0" : trimH12;
                    infoProjectbudgetJs.setJscdzyjglf(new BigDecimal(trimH12).multiply(new BigDecimal("10000")));
                    //项目建设管理费	总算表(表一)-H13
                    Row rowData3 = sheet1.getRow(12);
                    String trimH13 = getCellFormatValue(rowData3.getCell(7)).trim();
                    trimH13 = Strings.isBlank(trimH13) ? "0" : trimH13;
                    infoProjectbudgetJs.setXmjsglf(new BigDecimal(trimH13).multiply(new BigDecimal("10000")));
                    //建设期贷款利息	总算表(表一)-H22
                    Row rowDatajsqdk = sheet1.getRow(21);
                    String trimH22 = getCellFormatValue(rowDatajsqdk.getCell(7)).trim();
                    trimH22 = Strings.isBlank(trimH22) ? "0" : trimH22;
                    infoProjectbudgetJs.setJsqdklx(new BigDecimal(trimH22).multiply(new BigDecimal("10000")));
                } else {
                    infoProjectbudgetJs.setJzgcf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setAzgcf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSbgzf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setQtf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setTotal(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJscdzyjglf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setXmjsglf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJsqdklx(BigDecimal.ZERO);
                }

                if (sheet2 != null) {
                    for (Row cell : sheet2) {
                        String proName = getCellFormatValue(cell.getCell(1));
//                System.err.println(proName);
                        if ("项目管理经费".equals(proName)) {
                            System.err.println(infoProjectbudgetJs.getXmgljf());
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setXmgljf(new BigDecimal(value));
                        } else if ("工程监理费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setGcjlf(new BigDecimal(value));
                        } else if ("项目前期工作费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setXmqqgzf(new BigDecimal(value));
                        } else if ("勘察费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setKcf(new BigDecimal(value));
                        } else if ("设计费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setSjf(new BigDecimal(value));
                        } else if ("设计文件评审费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setSjwjpsf(new BigDecimal(value));
                        } else if ("非电缆工程项目后评价费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setFdlgcxmhpjf(new BigDecimal(value));
                        } else if ("电缆工程项目后评价费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setDlgcxmhpjf(new BigDecimal(value));
                        } else if ("工程保险费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setGcbxf(new BigDecimal(value));
                        } else if ("施工过程造价咨询及竣工结算审核费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setJgjsshf(new BigDecimal(value));
                        } else if ("生产准备费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setSczbf(new BigDecimal(value));
                        }
                    }
                    //遍历后对以上数据未获取到的，进行补0
                    infoProjectbudgetJs = setDefaultValue(infoProjectbudgetJs);
                } else {
                    infoProjectbudgetJs.setXmgljf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setGcjlf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSgf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setXmqqgzf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setKcf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSjf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSjwjpsf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setFdlgcxmhpjf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setDlgcxmhpjf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setGcbxf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJgjsshf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSczbf(BigDecimal.ZERO);
                }

                if (sheet3 != null) {
                    for (Row cells : sheet3) {
                        String proName = getCellFormatValue(cells.getCell(1));
                        if ("合计".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cells.getCell(4)).trim()) ? "0" : getCellFormatValue(cells.getCell(4)).trim();
                            infoProjectbudgetJs.setCcgcfhj(new BigDecimal(value));
                        }
                    }
                    if (infoProjectbudgetJs.getCcgcfhj() == null) {
                        infoProjectbudgetJs.setCcgcfhj(BigDecimal.ZERO);
                    }
                } else {
                    infoProjectbudgetJs.setCcgcfhj(BigDecimal.ZERO);
                }

                if (sheet4 != null) {
                    for (Row cells : sheet4) {
                        String proName = getCellFormatValue(cells.getCell(1));
                        if ("合计".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cells.getCell(11)).trim()) ? "0" : getCellFormatValue(cells.getCell(11)).trim();
                            infoProjectbudgetJs.setJgchj(new BigDecimal(value));
                            infoProjectbudgetJs.setJgchjje(new BigDecimal(value));
                        }
                    }
                    if (infoProjectbudgetJs.getJgchj() == null) {
                        infoProjectbudgetJs.setJgchj(BigDecimal.ZERO);
                        infoProjectbudgetJs.setJgchjje(BigDecimal.ZERO);
                    }
                } else {
                    infoProjectbudgetJs.setJgchj(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJgchjje(BigDecimal.ZERO);
                }

                if (sheet5 != null) {
                    for (Row cells : sheet5) {
                        String proName = getCellFormatValue(cells.getCell(1));
                        if ("带电作业".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cells.getCell(6)).trim()) ? "0" : getCellFormatValue(cells.getCell(6)).trim();
                            infoProjectbudgetJs.setDdzyf(new BigDecimal(value));
                        }
                    }
                    if (infoProjectbudgetJs.getDdzyf() == null) {
                        infoProjectbudgetJs.setDdzyf(BigDecimal.ZERO);
                    }
                } else {
                    infoProjectbudgetJs.setDdzyf(BigDecimal.ZERO);
                }

                //项目后评价费	非电缆工程项目后评价费+电缆工程项目后评价费
                infoProjectbudgetJs.setXmhpjf(infoProjectbudgetJs.getFdlgcxmhpjf().add(infoProjectbudgetJs.getDlgcxmhpjf()));
                //施工费	建筑工程费+安装工程费+拆除工程费-甲供材
                //拆除工程取费表—合计
                //甲供主材汇总表-合计
//                infoProjectbudgetJs.setSgf(infoProjectbudgetJs.getJzgcf().add(infoProjectbudgetJs.getAzgcf()).add(infoProjectbudgetJs.getCcgcfhj()).subtract(infoProjectbudgetJs.getJgchj()));
                infoProjectbudgetJs.setSgf(infoProjectbudgetJs.getJzgcf().add(infoProjectbudgetJs.getAzgcf()).subtract(infoProjectbudgetJs.getJgchj()));
                //=============数据封装=====================
                infoProjectbudgetJs.setId(SnowflakeIdWorker.getUUID());//ID
                infoProjectbudgetJs.setFiledate(new Date());
                infoProjectbudgetJs.setType(2);
                infoProjectbudgetJs.setCreatetype(1);
                infoProjectbudgetJs.setIsconfirm(0);
                infoProjectbudgetJs.setFilename(name);
            }

        }
        // 关闭流
        inputStream.close();
        // 返回list结果
        return infoProjectbudgetJs;
    }

    public Map<String, Object> uploadJgBatch(MultipartFile[] files, String batchnum, Integer type) {
        //0、初始化
        Map<String, Object> map = new HashMap<>();
        int total = files.length;//总数
        int successfulCount = 0;//成功条数
        int failureCount = 0;//失败条数
        InfoImportresult infoImportresult = new InfoImportresult();
        infoImportresult.setId(SnowflakeIdWorker.getUUID());//先生成主表id供插入明细表用

        //1、遍历进行数据导入、文件转换
        for (MultipartFile file : files) {
            String realname = file.getOriginalFilename();//导入原始文件名称
            //特殊符号正则处理
            String filename = this.repairName(realname);//导入原始文件名称

            InfoImportresultDtl infoImportresultDtl = new InfoImportresultDtl();
            infoImportresultDtl.setId(SnowflakeIdWorker.getUUID());
            infoImportresultDtl.setFilename(filename);
            infoImportresultDtl.setMainid(infoImportresult.getId());
            infoImportresultDtl.setBatchnum(batchnum);
            infoImportresultDtl.setCreatetime(new Date());
            infoImportresultDtl.setStep(3);
            infoImportresultDtl.setType(type);

            //文件做重复性校验
            InfoProjectbudgetJs budget = new InfoProjectbudgetJs();
            budget.setProname(filename.trim());
            //判别名称是否重复
            Boolean isexist = this.findisexist(budget);
            if (isexist) {
                failureCount++;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("名称为《" + realname + "》的文件已经导入，无法重复导入");
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
                continue;
            }

            // 1、导入数据获取--解析excel表格进行数据灌入
            InfoProjectbudgetJs infoProjectbudgetJs = null;
            try {
                infoProjectbudgetJs = importExcelJg(file.getInputStream(), file.getOriginalFilename());
                infoProjectbudgetJs.setBatchnum(batchnum);
            } catch (Exception e) {
                e.printStackTrace();
                failureCount++;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("Error processing file " + file.getOriginalFilename() + ": " + e.getMessage());
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
                continue;
            }

            //上传文件至服务器
            String pathString = null;
            String pdfpathString = null;
            try {
                pathString = null;
                String uuid = UUID.randomUUID().toString();
                Calendar calendar = Calendar.getInstance();
                String tm = calendar.get(Calendar.YEAR) + "-" + (calendar.get(Calendar.MONTH) + 1);
                Long size = file.getSize();
                //获取上传的文件名称
                //chrome会获取到该文件的直接文件名称，IE/Edge会获取到文件上传时完整路径/文件名
                //Check for Unix-style path
                int unixSep = filename.lastIndexOf('/');
                //Check for Windows-style path
                int winSep = filename.lastIndexOf('\\');
                //cut off at latest possible point
                int pos = (winSep > unixSep ? winSep : unixSep);
                if (pos != -1) {
                    filename = filename.substring(pos + 1);
                }
                String pdffilename = filename.substring(0, filename.lastIndexOf("."));
                pathString = uploadPath + tm + singleSlash + uuid + singleSlash + filename;
                pdfpathString = uploadPath + tm + singleSlash + uuid + singleSlash + pdffilename + ".pdf";
                File orifiles = new File(pathString);
                if (!orifiles.getParentFile().exists()) {
                    orifiles.getParentFile().mkdirs();
                }
                file.transferTo(orifiles);
                map.put("code", 0);
                map.put("fileUrl", staticAccessPath + tm + singleSlash + uuid + singleSlash + filename);
                map.put("fileName", filename);
                map.put("localPath", pathString);
                map.put("size", size);

                //业务文件表中增加excel文件记录
                createFileExcel(pathString, map, realname, infoProjectbudgetJs);
                //Excel转换为PDF上传至服务器文件夹
                TestForExcel2PDF.excel2pdf(pathString, pdfpathString);
                //业务文件表中增加pdf文件记录
                createFilePdf(pdfpathString, map, realname, infoProjectbudgetJs);
            } catch (IOException e) {
                e.printStackTrace();
                failureCount++;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("附件上传服务器失败" + e.getMessage());
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
                continue;
            }

            //插入业务表
            if (infoProjectbudgetJsMapper.insert(infoProjectbudgetJs) > 0) {
                successfulCount = successfulCount + 1;
                infoImportresultDtl.setStatus(1);
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
            } else {
                failureCount = failureCount + 1;
                //结果插入导入操作明细表
                infoImportresultDtl.setStatus(0);
                infoImportresultDtl.setReason("新增插入至概算表失败");
                infoImportresultDtlMapper.insertSelective(infoImportresultDtl);
            }

        }

        //2、导入结果主表及明细表导入
        infoImportresult.setBatchnum(batchnum);
        infoImportresult.setTotal(total);
        infoImportresult.setSuccesscount(successfulCount);
        infoImportresult.setFailcount(failureCount);
        infoImportresult.setCreatetime(new Date());
        infoImportresult.setStep(3);
        infoImportresult.setType(type);
        infoImportresultMapper.insertSelective(infoImportresult);
        //3、最终结果返回
        map.put("code", 0);
        if (failureCount > 0) {
            map.put("msg", "本次导入共选择" + files.length + "项技改类概算书,成功导入" + successfulCount + "项，失败" + failureCount + "项");
        } else {
            map.put("msg", "本次导入共选择" + files.length + "项技改类概算书,全部导入成功");
        }
        return map;
    }

    public InfoProjectbudgetJs importExcelJg(InputStream inputStream, String name) throws Exception {
        InfoProjectbudgetJs infoProjectbudgetJs = new InfoProjectbudgetJs();
        // 1、传入获取的工作流, workbook,是一个工作簿，使用excel能操作的这边它都可以操作，这一个操作，相当于是把Excel放到Java中使用
        // Workbook wb = new HSSFWorkbook(inputStream);
        Workbook wb = null;
        String extString = name.substring(name.lastIndexOf("."));
        if (".xls".equals(extString)) {
            wb = new HSSFWorkbook(inputStream);
        } else if (".xlsx".equals(extString)) {
            wb = new XSSFWorkbook(inputStream);
        }
        // 2、获取工作簿中的表,对表中的设置，通过下标去拿第一张表
//        Sheet sheet = wb.getSheetAt(0);//封面0
        Sheet sheet = wb.getSheet("封面");//封面0
        Sheet sheet1 = wb.getSheet("技术改造工程总表(表一)");//技改改造工程总表(表一)3
        Sheet sheet2 = wb.getSheet("其他费用表(表四)");//其他费用表(表四)13
        Sheet sheet3 = wb.getSheet("拆除工程取费表");//拆除工程取费表
        Sheet sheet4 = wb.getSheet("甲供未计价材料汇总表(附表三)");//甲供未计价材料汇总表(附表三)
        Sheet sheet5 = wb.getSheet("技术改造安装工程专业汇总表(表二甲)");

        if (sheet != null) {
            //概算书名称--封面--A4
            Row rowData = sheet.getRow(3);
            String proname = getCellFormatValue(rowData.getCell(0));
            int i = proname.indexOf("：");
            infoProjectbudgetJs.setProname(proname.substring(proname.indexOf("：") + 1));
            //概算书日期--封面--A7
            Row rowData1 = sheet.getRow(6);
            String bookdate = getCellFormatValue(rowData1.getCell(0));
            bookdate = bookdate.substring(bookdate.indexOf("：") + 1);
//            String format = sdf.format(bookdate.trim().toString());
            infoProjectbudgetJs.setBookdate(bookdate);
//            Cell cell = rowData1.getCell(0);
//            System.err.println(cell.getCellType());
//            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
//                if (DateUtil.isCellDateFormatted(cell)) {
//                    //这里一直没进来，待观测
//                    String cellFormatValue = getCellFormatValue(cell).trim();
//                    infoProjectbudgetJs.setBookdate(cellFormatValue);
//                } else {
//                    Date date = cell.getDateCellValue();
//                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
//                    String format = sdf.format(date);
//                    infoProjectbudgetJs.setBookdate(format);
//                }
//            } else {
//                String cellFormatValue = getCellFormatValue(cell).trim();
//                infoProjectbudgetJs.setBookdate(cellFormatValue);
//            }
        } else {
            infoProjectbudgetJs.setProname(null);
            infoProjectbudgetJs.setBookdate(null);
        }

        if (Strings.isBlank(infoProjectbudgetJs.getProname())) {
            throw new FileNameException(500, "概算书导入异常，概算书项目名称为空");
        } else {
            //根据概算书封面名称判断是否重复导入
            InfoProjectbudgetJsExample infoProjectbudgetJsExample = new InfoProjectbudgetJsExample();
            infoProjectbudgetJsExample.createCriteria().andPronameEqualTo(infoProjectbudgetJs.getProname());
            List<InfoProjectbudgetJs> infoProjectbudgetJss = infoProjectbudgetJsMapper.selectByExample(infoProjectbudgetJsExample);
            if (infoProjectbudgetJss != null && infoProjectbudgetJss.size() > 0) {
                throw new FileNameException(500, "概算书导入异常，概算书项目名称重复无法重复导入");
            } else {
                if (sheet1 != null) {
                    //建筑工程费--技改改造工程总表(表一)-C4
                    Row rowDatajzgcf = sheet1.getRow(3);
                    String trimC23 = getCellFormatValue(rowDatajzgcf.getCell(2)).trim();
                    trimC23 = Strings.isBlank(trimC23) ? "0" : trimC23;
                    infoProjectbudgetJs.setJzgcf(new BigDecimal(trimC23).multiply(new BigDecimal("10000")));
                    //安装工程费  技改改造工程总表(表一)-C5
                    Row rowDataAzgcf = sheet1.getRow(4);
                    String trimE23 = getCellFormatValue(rowDataAzgcf.getCell(2)).trim();
                    trimE23 = Strings.isBlank(trimE23) ? "0" : trimE23;
                    infoProjectbudgetJs.setAzgcf(new BigDecimal(trimE23).multiply(new BigDecimal("10000")));
                    //拆除工程费  技术改造工程总表(表一)-C6
                    Row rowDataCcgcf = sheet1.getRow(5);
                    String trimCcgcf = getCellFormatValue(rowDataCcgcf.getCell(2)).trim();
                    trimCcgcf = Strings.isBlank(trimCcgcf) ? "0" : trimCcgcf;
                    infoProjectbudgetJs.setCcgcfhj(new BigDecimal(trimCcgcf).multiply(new BigDecimal("10000")));
                    //设备购置费  技改改造工程总表(表一)-C7
                    Row rowDataSbgzf = sheet1.getRow(6);
                    String trimD23 = getCellFormatValue(rowDataSbgzf.getCell(2)).trim();
                    trimD23 = Strings.isBlank(trimD23) ? "0" : trimD23;
                    infoProjectbudgetJs.setSbgzf(new BigDecimal(trimD23).multiply(new BigDecimal("10000")));
                    //甲供设备材料费  技术改造工程总表(表一)-C10
//                    Row rowDataJgchj = sheet1.getRow(9);
//                    String trimJgchj = getCellFormatValue(rowDataJgchj.getCell(2)).trim();
//                    trimJgchj = Strings.isBlank(trimJgchj) ? "0" : trimJgchj;
//                    infoProjectbudgetJs.setJgchj(new BigDecimal(trimJgchj).multiply(new BigDecimal("10000")));
                    //其他费	技改改造工程总表(表一)-C11
                    Row rowDataQtf = sheet1.getRow(10);
                    String trimF23 = getCellFormatValue(rowDataQtf.getCell(2)).trim();
                    trimF23 = Strings.isBlank(trimF23) ? "0" : trimF23;
                    infoProjectbudgetJs.setQtf(new BigDecimal(trimF23).multiply(new BigDecimal("10000")));
                    //总投资 技改改造工程总表(表一)-C14
                    Row rowDataTotal = sheet1.getRow(13);
                    String trimH23 = getCellFormatValue(rowDataTotal.getCell(2)).trim();
                    trimH23 = Strings.isBlank(trimH23) ? "0" : trimH23;
                    infoProjectbudgetJs.setTotal(new BigDecimal(trimH23).multiply(new BigDecimal("10000")));
                    //建设场地征用及清理费	其他费用表(表四)
//                    Row rowData2 = sheet1.getRow(11);
//                    String trimH12 = getCellFormatValue(rowData2.getCell(7)).trim();
//                    trimH12 = Strings.isBlank(trimH12) ? "0" : trimH12;
//                    infoProjectbudgetJs.setJscdzyjglf(new BigDecimal(trimH12).multiply(new BigDecimal("10000")));
                    infoProjectbudgetJs.setJscdzyjglf(BigDecimal.ZERO);
                    //项目建设管理费	默认取0
//                    Row rowData3 = sheet1.getRow(12);
//                    String trimH13 = getCellFormatValue(rowData3.getCell(7)).trim();
//                    trimH13 = Strings.isBlank(trimH13) ? "0" : trimH13;
//                    infoProjectbudgetJs.setXmjsglf(new BigDecimal(trimH13).multiply(new BigDecimal("10000")));
                    infoProjectbudgetJs.setXmjsglf(BigDecimal.ZERO);
                    //建设期贷款利息	默认取0
//                    Row rowDatajsqdk = sheet1.getRow(21);
//                    String trimH22 = getCellFormatValue(rowDatajsqdk.getCell(7)).trim();
//                    trimH22 = Strings.isBlank(trimH22) ? "0" : trimH22;
//                    infoProjectbudgetJs.setJsqdklx(new BigDecimal(trimH22).multiply(new BigDecimal("10000")));
                    infoProjectbudgetJs.setJsqdklx(BigDecimal.ZERO);
                } else {
                    infoProjectbudgetJs.setJzgcf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setAzgcf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSbgzf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setQtf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setTotal(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJscdzyjglf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setXmjsglf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJsqdklx(BigDecimal.ZERO);
                }

                if (sheet2 != null) {
                    for (Row cell : sheet2) {
                        String proName = getCellFormatValue(cell.getCell(1));
//                System.err.println(proName);
                        if ("管理经费".equals(proName)) {
                            System.err.println(infoProjectbudgetJs.getXmgljf());
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setXmgljf(new BigDecimal(value));
                        } else if ("工程监理费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setGcjlf(new BigDecimal(value));
                        } else if ("前期工作费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setXmqqgzf(new BigDecimal(value));
                        } else if ("勘察费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setKcf(new BigDecimal(value));
                        } else if ("设计费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setSjf(new BigDecimal(value));
                        } else if ("设计文件评审费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setSjwjpsf(new BigDecimal(value));
                        } else if ("非电缆工程项目后评价费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setFdlgcxmhpjf(new BigDecimal(value));
                        } else if ("电缆工程项目后评价费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setDlgcxmhpjf(new BigDecimal(value));
                        } else if ("工程保险费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setGcbxf(new BigDecimal(value));
                        } else if ("施工过程造价咨询及竣工结算审核费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setJgjsshf(new BigDecimal(value));
                        } else if ("生产准备费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setSczbf(new BigDecimal(value));
                        } else if ("建设场地征用及清理费".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cell.getCell(3)).trim()) ? "0" : getCellFormatValue(cell.getCell(3)).trim();
                            infoProjectbudgetJs.setJscdzyjglf(new BigDecimal(value));
                        }
                    }
                    //遍历后对以上数据未获取到的，进行补0
                    infoProjectbudgetJs = setDefaultValue(infoProjectbudgetJs);
                } else {
                    infoProjectbudgetJs.setXmgljf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setGcjlf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSgf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setXmqqgzf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setKcf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSjf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSjwjpsf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setFdlgcxmhpjf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setDlgcxmhpjf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setGcbxf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJgjsshf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setSczbf(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJscdzyjglf(BigDecimal.ZERO);
                }

//                if (sheet3 != null) {
//                    for (Row cells : sheet3) {
//                        String proName = getCellFormatValue(cells.getCell(1));
//                        if ("合计".equals(proName)) {
//                            String value = Strings.isBlank(getCellFormatValue(cells.getCell(5)).trim()) ? "0" : getCellFormatValue(cells.getCell(5)).trim();
//                            infoProjectbudgetJs.setCcgcfhj(new BigDecimal(value));
//                        }
//                    }
//                    if (infoProjectbudgetJs.getCcgcfhj() == null) {
//                        infoProjectbudgetJs.setCcgcfhj(BigDecimal.ZERO);
//                    }
//                } else {
//                    infoProjectbudgetJs.setCcgcfhj(BigDecimal.ZERO);
//                }
//
                if (sheet4 != null) {
                    for (Row cells : sheet4) {
                        String proName = getCellFormatValue(cells.getCell(2));
                        if ("合计：".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cells.getCell(10)).trim()) ? "0" : getCellFormatValue(cells.getCell(10)).trim();
                            infoProjectbudgetJs.setJgchj(new BigDecimal(value));
                            infoProjectbudgetJs.setJgchjje(new BigDecimal(value));
                        }
                    }
                    if (infoProjectbudgetJs.getJgchj() == null) {
                        infoProjectbudgetJs.setJgchj(BigDecimal.ZERO);
                        infoProjectbudgetJs.setJgchjje(BigDecimal.ZERO);
                    }
                } else {
                    infoProjectbudgetJs.setJgchj(BigDecimal.ZERO);
                    infoProjectbudgetJs.setJgchjje(BigDecimal.ZERO);
                }

                if (sheet5 != null) {
                    for (Row cells : sheet5) {
                        String proName = getCellFormatValue(cells.getCell(1));
                        if ("带电作业".equals(proName)) {
                            String value = Strings.isBlank(getCellFormatValue(cells.getCell(6)).trim()) ? "0" : getCellFormatValue(cells.getCell(6)).trim();
                            infoProjectbudgetJs.setDdzyf(new BigDecimal(value));
                        }
                    }
                    if (infoProjectbudgetJs.getDdzyf() == null) {
                        infoProjectbudgetJs.setDdzyf(BigDecimal.ZERO);
                    }
                } else {
                    infoProjectbudgetJs.setDdzyf(BigDecimal.ZERO);
                }

                //项目后评价费	非电缆工程项目后评价费+电缆工程项目后评价费
                infoProjectbudgetJs.setXmhpjf(infoProjectbudgetJs.getFdlgcxmhpjf().add(infoProjectbudgetJs.getDlgcxmhpjf()));
                //施工费	建筑工程费+安装工程费+拆除工程费-甲供材
                //拆除工程取费表—合计
                //甲供主材汇总表-合计
//                infoProjectbudgetJs.setSgf(infoProjectbudgetJs.getJzgcf().add(infoProjectbudgetJs.getAzgcf()).add(infoProjectbudgetJs.getCcgcfhj()).subtract(infoProjectbudgetJs.getJgchj()));
                infoProjectbudgetJs.setSgf(infoProjectbudgetJs.getJzgcf().add(infoProjectbudgetJs.getAzgcf()).add(infoProjectbudgetJs.getCcgcfhj()).subtract(infoProjectbudgetJs.getJgchj()));
                //=============数据封装=====================
                infoProjectbudgetJs.setId(SnowflakeIdWorker.getUUID());//ID
                infoProjectbudgetJs.setFiledate(new Date());
                infoProjectbudgetJs.setType(3);//技改
                infoProjectbudgetJs.setCreatetype(1);
                infoProjectbudgetJs.setIsconfirm(0);
                infoProjectbudgetJs.setFilename(name);
            }

        }
        // 关闭流
        inputStream.close();
        // 返回list结果
        return infoProjectbudgetJs;
    }

    public List<InfoProjectbudgetJs> selectProData(String year) {
        return infoProjectbudgetJsMapper.selectProData(year);
    }

    public List<InfoProjectbudgetJs> selectRangeData(String year) {
        return infoProjectbudgetJsMapper.selectRangeData(year);
    }

    @Transactional
    public Map<String, Object> uploadBindNum(MultipartFile file) {

        //获取当前登录人员信息，获取角色与对应id
        TsysUser currentUser = ShiroUtils.getUser();
        Map<String, Object> map = new HashMap<>();
        // 0、初始化、定义执行结果变量
        String returnStr = "";
        int total = 0;// 总条数
        int successNum = 0;// 成功条数
        int failNum = 0;// 失败条数
        int passNum = 0;// 跳过条数
        String realname = file.getOriginalFilename();//导入原始文件名称
        //特殊符号正则处理
        String filename = UploadFileUtil.repairName(realname);//导入原始文件名称经过特殊符号处理后名称
        List<InfoProjectbudgetJs> list = new ArrayList<>();
        // 1、导入数据获取
        try {
            list = importExcelList(file.getInputStream(), filename);
            List<InfoProjectbudgetJs> numList = list.stream().filter(s -> StringUtils.isNotBlank(s.getPronum())).collect(Collectors.toList());
            boolean hasDuplicatesInList1 = checkDuplicateStudentIds(numList);
            if (hasDuplicatesInList1) {
                map.put("code", 1);
                map.put("msg", "模板内存在重复编号，请检查修改后重新进行导入绑定");
                return map;
            } else {
                //验证环节
                list = list.stream().filter(s -> "结算".equals(s.getHuanjie()) && StringUtils.isNotBlank(s.getProname())).collect(Collectors.toList());
                //验证模板内编号是否重复
                //验证模板内编号与现有编号是否重复
                for (InfoProjectbudgetJs infoProjectbudget : list) {
                    InfoProjectbudgetJsExample infoProjectbudgetJsExample = new InfoProjectbudgetJsExample();
                    infoProjectbudgetJsExample.createCriteria().andPronameEqualTo(infoProjectbudget.getProname());
                    List<InfoProjectbudgetJs> infoProjectbudgets = infoProjectbudgetJsMapper.selectByExample(infoProjectbudgetJsExample);
                    if (infoProjectbudgets != null && infoProjectbudgets.size() == 1) {
                        InfoProjectbudgetJs needtoupdate = infoProjectbudgets.get(0);
                        if (StringUtils.isBlank(needtoupdate.getPronum())) {
                            if (StringUtils.isBlank(infoProjectbudget.getPronum())) {
                                continue;
                            } else {
                                if (!infoProjectbudget.getPronum().equals(needtoupdate.getPronum())) {
                                    needtoupdate.setPronum(infoProjectbudget.getPronum());
                                    needtoupdate.setUpdatetime(new Date());
                                    needtoupdate.setUpdateuser(currentUser.getId());
                                    int i = infoProjectbudgetJsMapper.updateByPrimaryKey(needtoupdate);
                                    if (i > 0) {
                                        successNum += 1;
                                    }
                                }
                            }
                        } else {
                            if (!needtoupdate.getPronum().equals(infoProjectbudget.getPronum())) {
                                needtoupdate.setPronum(infoProjectbudget.getPronum());
                                needtoupdate.setUpdatetime(new Date());
                                needtoupdate.setUpdateuser(currentUser.getId());
                                int i = infoProjectbudgetJsMapper.updateByPrimaryKey(needtoupdate);
                                if (i > 0) {
                                    successNum += 1;
                                }
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        total = list.size();
        failNum = total - successNum;

        map.put("code", 0);
        map.put("msg", "导入总数据[" + total + "]条,成功绑定编号[" + successNum + "]条");
        return map;
    }

    /*
     * @Title: importExcelList
     * @Author: ShengQL
     * @Date: 2025/4/1 13:35
     * @Params: [inputStream, filename]
     * @Return: List<InfoProject>
     * @Description: 项目导入模板数据获取封装
     */
    public List<InfoProjectbudgetJs> importExcelList(InputStream inputStream, String filename) throws Exception {

        // 定义一个List，用来存放excel表中的值
        List<InfoProjectbudgetJs> resultList = new ArrayList<InfoProjectbudgetJs>();

        // 1、传入获取的工作流, workbook,是一个工作簿，使用excel能操作的这边它都可以操作，这一个操作，相当于是把Excel放到Java中使用
        Workbook wb = null;
        String extString = filename.substring(filename.lastIndexOf("."));
        if (".xls".equals(extString)) {
            wb = new HSSFWorkbook(inputStream);
        } else if (".xlsx".equals(extString)) {
            wb = new XSSFWorkbook(inputStream);
        }
        // 2、获取工作簿中的表,对表中的设置，通过下标去拿第一张表
        Sheet sheet = wb.getSheetAt(0);
        // 3、获取表格总的行数
        int rowNumber = sheet.getPhysicalNumberOfRows();

        // 从第二行开始读取每一行数据，我们设置的模板表头是在第一、第二行，下标从0开始。
        for (int rowNum = 1; rowNum < rowNumber; rowNum++) {
            InfoProjectbudgetJs infoProjectbudget = new InfoProjectbudgetJs();
            // 获取工作表中第二行的数据
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                infoProjectbudget.setPronum(UploadFileUtil.importExcel(rowData.getCell(0), wb));
                infoProjectbudget.setProname(UploadFileUtil.importExcel(rowData.getCell(1), wb));
                infoProjectbudget.setHuanjie(UploadFileUtil.importExcel(rowData.getCell(7), wb));
            }
            resultList.add(infoProjectbudget);
        }
        // 循环结束后，把从excel表读取的对象放到excelList中。
        // 关闭流
        inputStream.close();

        return resultList;
    }

    /**
     * 检查列表中是否有重复的学号
     */

    public static boolean checkDuplicateStudentIds(List<InfoProjectbudgetJs> students) {
        // 按学号分组，然后检查是否有任何学号对应多个学生
        return students.stream()
                .collect(Collectors.groupingBy(
                        InfoProjectbudgetJs::getPronum,
                        Collectors.counting()
                ))
                .values()
                .stream()
                .anyMatch(count -> count > 1);
    }

    public int checkOrderUniqueAdd(InfoProjectbudgetJs infoProjectbudgetJs) {
        InfoProjectbudgetJsExample infoProjectbudgetJsExample = new InfoProjectbudgetJsExample();
        infoProjectbudgetJsExample.createCriteria().andPronumEqualTo(infoProjectbudgetJs.getPronum());
        return infoProjectbudgetJsMapper.selectByExample(infoProjectbudgetJsExample).size();
    }
}